package com.hnl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hnl.core.connection.ConnectionManager;
import com.hnl.core.connection.ConnectionManagerFactory;
import com.hnl.to.TalentEducation;

public class DBTalentEducationDAO implements TalentEducationDAO {
	public static final Logger LOG = LoggerFactory
			.getLogger(DBTalentEducationDAO.class);
	private PreparedStatement saveStmt;
	private PreparedStatement loadStmt;
	private PreparedStatement loadOneTalentStmt;
	private PreparedStatement updateStmt;
	private PreparedStatement delOneTalentTaEduStmt;

	protected DBTalentEducationDAO() throws SQLException {
		// Empty protected constructor to avoid unnecessary initialization
	}

	public TalentEducation saveTalentEducation(TalentEducation talenteducation) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			saveStmt = conn
					.prepareStatement(
							"INSERT INTO talent_education (talent_ID, degree, school, major, duration, isdelete ) "
									+ "VALUES (?,?,?,?,?,?)",
							PreparedStatement.RETURN_GENERATED_KEYS);
			saveStmt.setInt(1, talenteducation.getTalentID());
			saveStmt.setString(2, talenteducation.getDegree());
			saveStmt.setString(3, talenteducation.getSchool());
			saveStmt.setString(4, talenteducation.getMajor());
			saveStmt.setString(5, talenteducation.getDuration());
			saveStmt.setBoolean(6, talenteducation.getIsdelete());
			saveStmt.executeUpdate();

			ResultSet rs = saveStmt.getGeneratedKeys();
			if (rs.next()) {
				talenteducation.setTalentEducationID(rs.getInt(1));
			}
			rs.close();
			saveStmt.close();
		} catch (SQLException e) {
			LOG.error("Error saving TalentEducation.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return talenteducation;
	}

	public TalentEducation loadTalentEducation(Integer talenteducationID) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		TalentEducation talenteducation = null;
		try {
			loadStmt = conn
					.prepareStatement("SELECT talent_education_ID, talent_ID, degree, school, major, duration, isdelete FROM talent_education "
							+ "WHERE talent_education_ID = ?");
			loadStmt.setInt(1, talenteducationID);
			ResultSet rs = loadStmt.executeQuery();
			if (rs.next()) {
				talenteducation = new TalentEducation();
				talenteducation.setTalentEducationID(rs
						.getInt("talent_education_ID"));
				talenteducation.setTalentID(rs.getInt("talent_ID"));
				talenteducation.setDegree(rs.getString("degree"));
				talenteducation.setSchool(rs.getString("school"));
				talenteducation.setMajor(rs.getString("major"));
				talenteducation.setDuration(rs.getString("duration"));
				talenteducation.setIsdelete(rs.getBoolean("isdelete"));
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error load TalentEducation.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return talenteducation;
	}

	public Collection<TalentEducation> loadOneTalentTalentEducation(
			Integer talentID) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Collection<TalentEducation> talenteducationCollection = new Vector<TalentEducation>();
		try {
			loadOneTalentStmt = conn
					.prepareStatement("SELECT talent_education_ID, talent_ID, degree, school, major, duration, isdelete FROM talent_education "
							+ "WHERE talent_ID=? AND isdelete=false");
			loadOneTalentStmt.setInt(1, talentID);
			ResultSet rs = loadOneTalentStmt.executeQuery();
			while (rs.next()) {
				TalentEducation talenteducation = new TalentEducation();
				talenteducation.setTalentEducationID(rs
						.getInt("talent_education_ID"));
				talenteducation.setTalentID(rs.getInt("talent_ID"));
				talenteducation.setDegree(rs.getString("degree"));
				talenteducation.setSchool(rs.getString("school"));
				talenteducation.setMajor(rs.getString("major"));
				talenteducation.setDuration(rs.getString("duration"));
				talenteducation.setIsdelete(rs.getBoolean("isdelete"));
				talenteducationCollection.add(talenteducation);
			}
			rs.close();
			loadOneTalentStmt.close();
		} catch (SQLException e) {
			LOG.error("Error load One Talent TalentEducation.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return talenteducationCollection;
	}

	public void updateTalentEducation(TalentEducation talenteducation) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			updateStmt = conn
					.prepareStatement("UPDATE talent_education SET talent_ID=?, degree=?, school=?, major=?, duration=?, isdelete=?"
							+ " WHERE talent_education_ID=?");
			updateStmt.setInt(1, talenteducation.getTalentID());
			updateStmt.setString(2, talenteducation.getDegree());
			updateStmt.setString(3, talenteducation.getSchool());
			updateStmt.setString(4, talenteducation.getMajor());
			updateStmt.setString(5, talenteducation.getDuration());
			updateStmt.setBoolean(6, talenteducation.getIsdelete());
			updateStmt.setString(7, talenteducation.getTalentEducationID()
					.toString());
			updateStmt.executeUpdate();
			updateStmt.close();
		} catch (SQLException e) {
			LOG.error("Error update Talent Education.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

	public void deleteTalentEducation(Integer talenteducationID) {
		TalentEducation talenteducation = loadTalentEducation(talenteducationID);
		talenteducation.setIsdelete(true);
		updateTalentEducation(talenteducation);
	}

	public void deleteOneTalentTalentEducation(Integer talentID) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			delOneTalentTaEduStmt = conn
					.prepareStatement("UPDATE talent_education SET isdelete=true"
							+ " WHERE talent_ID=?");
			delOneTalentTaEduStmt.setInt(1, talentID);
			delOneTalentTaEduStmt.executeUpdate();
			delOneTalentTaEduStmt.close();
		} catch (SQLException e) {
			LOG.error("Error delete One Talent TalentEducation.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}
}
